from util.sql_pyodbc import SQLServer_pyodbc
from typing import List, Dict, Tuple, Optional, Any
import datetime
import logging

class ItemBrandService:
    """物料品牌签单服务类"""
    
    def __init__(self, database="AnJiServer"):
        """初始化数据库连接"""
        self.db = SQLServer_pyodbc(database)
        self.logger = logging.getLogger(__name__)
    
    def get_pl_items(self, picking_id: str, itype: int) -> Optional[List[Dict]]:
        """
        查询识别码对应的所有的编码
        Args:
            picking_id: 识别码
            itype: 0_自制件, 1_外购件
        Returns:
            编码列表
        """
        sql = """
        select Item, sum(Qty) as Qty 
        from PL_Item 
        where Con_PickingID=? and Itype=? 
        group by Item
        """
        try:
            rows = self.db.Query_all(sql, (picking_id, itype))
            if not rows:
                return None
            
            result = []
            for row in rows:
                result.append({
                    "item": row.Item,
                    "qty": int(row.Qty),
                    "signed_qty": 0  # 默认已签数量为0
                })
            return result
        except Exception as e:
            print(f"获取物料列表出错: {e}")
            return None
    
    def get_item_brand(self, item: str) -> Optional[List[Dict]]:
        """
        查询编码对应的所有的编码信息
        Args:
            item: 编码
        Returns:
            品牌信息列表
        """
        sql = "select Item, Brand, Model, Origin, 1 as Qty from Item_Brand where Item=?"
        try:
            rows = self.db.Query_all(sql, (item,))
            if not rows:
                return None
            
            result = []
            for row in rows:
                result.append({
                    "item": row.Item,
                    "brand": row.Brand,
                    "model": row.Model,
                    "specification": "",  # 添加规格字段，默认为空字符串
                    "origin": row.Origin,
                    "quantity": int(row.Qty)  # 将qty改为quantity
                })
            return result
        except Exception as e:
            print(f"获取品牌信息出错: {e}")
            return None
    
    def get_con_picking_id(self, picking_id: str) -> Tuple[int, str, str]:
        """
        判断合同号是自制件还是外购件
        Args:
            picking_id: 识别码
        Returns:
            (结果标识, 国家ID, 合同号)
            结果标识: -1表示国家资料表中没有此国家代码, 0表示不需要签单, 1表示需要签单
        """
        sql = """
        SELECT A.ContractNO, B.CountryID, B.ZZJSign 
        FROM Con_Picking AS A 
        LEFT JOIN Country AS B ON A.CountryCode=B.CountryID 
        where Con_PickingID=?
        """
        try:
            row = self.db.Query_one(sql, (picking_id,))
            if not row:
                return -1, "", ""
            
            return int(row.ZZJSign), row.CountryID, row.ContractNO
        except Exception as e:
            print(f"获取签单信息出错: {e}")
            return -1, "", ""
    
    def bool_sign_data(self, picking_id: str) -> bool:
        """
        判断签单数据表中有没有提交的识别码数据
        Args:
            picking_id: 识别码
        Returns:
            True表示已存在签单数据, False表示不存在
        """
        sql = "SELECT COUNT(SignDataID) AS SID FROM SignData where Con_PickingID=?"
        try:
            row = self.db.Query_one(sql, (picking_id,))
            return int(row.SID) > 0
        except Exception as e:
            print(f"检查签单数据出错: {e}")
            return False
    
    def position_num(self, contract_no: str) -> int:
        """
        判断合同号还有多少份单没有签单
        
        Args:
            contract_no: 合同号
            
        Returns:
            未完成签单的数量
        """
        try:
            sql = f"SELECT COUNT(Con_PickingID) AS num FROM Con_Picking WHERE ContractNO='{contract_no}' AND PackageQtyStatus !=6"
            result = self.db.Query_one(sql)
            if result and hasattr(result, 'num'):
                return int(result.num)
            return 0
        except Exception as e:
            self.logger.error(f"获取未签单数量失败: {str(e)}")
            raise e
    
    def con_is_photo(self, contract_no: str) -> int:
        """
        判断合同是否需要拍照
        
        Args:
            contract_no: 合同号
            
        Returns:
            如果需要拍照返回5，否则返回6
        """
        try:
            sql = f"SELECT COUNT(Item) AS sumitem FROM ConItem WHERE ContractNO='{contract_no}' AND ISPhoto='Y'"
            result = self.db.Query_one(sql)
            if result and hasattr(result, 'sumitem') and int(result.sumitem) > 0:
                return 5
            return 6
        except Exception as e:
            self.logger.error(f"判断合同是否需要拍照失败: {str(e)}")
            raise e
    
    def get_contracts(self) -> List[str]:
        """
        获取需要签单的合同列表
        Returns:
            合同号列表
        """
        sql = "SELECT DISTINCT(ContractNO) AS ContractNO FROM Con_Picking WHERE PackageQtyStatus=5"
        try:
            rows = self.db.Query_all(sql)
            if not rows:
                return []
            
            return [row.ContractNO for row in rows]
        except Exception as e:
            print(f"获取合同列表出错: {e}")
            return []
    
    def get_picking_ids(self, contract_no: str) -> List[str]:
        """
        根据合同号获取识别码列表
        Args:
            contract_no: 合同号
        Returns:
            识别码列表
        """
        sql = "SELECT Con_PickingID FROM Con_Picking WHERE ContractNO=? AND PackageQtyStatus=5"
        try:
            rows = self.db.Query_all(sql, (contract_no,))
            if not rows:
                return []
            
            return [row.Con_PickingID for row in rows]
        except Exception as e:
            print(f"获取识别码列表出错: {e}")
            return []
    
    def get_picking_status(self, picking_id: str) -> int:
        """
        获取识别码的状态
        Args:
            picking_id: 识别码
        Returns:
            状态码: -1不存在, 0-4未回齐套货位, 5已回齐套待签单, 6已签单
        """
        sql = "SELECT PackageQtyStatus FROM Con_Picking WHERE Con_PickingID=?"
        try:
            row = self.db.Query_one(sql, (picking_id,))
            if not row:
                return -1
            return int(row.PackageQtyStatus)
        except Exception as e:
            print(f"获取识别码状态出错: {e}")
            return -1
    
    def get_bill_data(self, picking_id: str) -> Optional[Dict]:
        """
        查询签单相关数据
        Args:
            picking_id: 识别码
        Returns:
            签单数据
        """
        # 1. 获取识别码状态
        status = self.get_picking_status(picking_id)
        if status == -1:
            return {"error": "识别码不存在"}
        elif status < 5:
            return {"error": "此识别码还未回齐套货位,不能进行签单"}
        elif status == 6:
            return {"error": "此识别码已签单,不能重复签单"}
        
        # 2. 获取合同号信息
        zzj_sign, country_id, contract_no = self.get_con_picking_id(picking_id)
        if contract_no == "":
            return {"error": f"获取合同号信息失败"}
        
        # 3. 获取自制件列表
        self_made_items = self.get_pl_items(picking_id, 0)
        self_made_items = self_made_items if self_made_items else []
        
        # 4. 获取外购件列表
        purchased_items = self.get_pl_items(picking_id, 1)
        purchased_items = purchased_items if purchased_items else []
        
        # 5. 计算数量统计
        self_made_total_qty = sum(item["qty"] for item in self_made_items)
        self_made_remaining_qty = self_made_total_qty  # 初始时未签数量等于总数量
        
        purchased_total_qty = sum(item["qty"] for item in purchased_items)
        purchased_remaining_qty = purchased_total_qty  # 初始时未签数量等于总数量
        
        return {
            "contract_no": contract_no,
            "self_made_items": self_made_items,
            "purchased_items": purchased_items,
            "self_made_total_qty": self_made_total_qty,
            "self_made_remaining_qty": self_made_remaining_qty,
            "purchased_total_qty": purchased_total_qty,
            "purchased_remaining_qty": purchased_remaining_qty
        }
        
    def submit_sign_data(self, data: Dict) -> Dict:
        """
        提交签单数据
        Args:
            data: 签单数据，包含picking_id, contract_no, brand_models等信息
        Returns:
            提交结果
        """
        picking_id = data.get("picking_id")
        contract_no = data.get("contract_no")
        brand_models = data.get("brand_models", [])
        work_order = data.get("work_order", "")
        
        # 1. 验证参数
        if not picking_id or not contract_no or not brand_models:
            return {"success": False, "message": "参数错误，缺少必要信息"}
        
        # 2. 检查识别码状态
        status = self.get_picking_status(picking_id)
        if status == -1:
            return {"success": False, "message": "识别码不存在"}
        elif status < 5:
            return {"success": False, "message": "此识别码还未回齐套货位,不能进行签单"}
        elif status == 6:
            return {"success": False, "message": "此识别码已签单,不能重复签单"}
        
        # 3. 检查是否已存在签单数据
        if self.bool_sign_data(picking_id):
            return {"success": False, "message": "数据库中已有此识别码的签单数据,不能重复提交"}
        
        # 4. 获取物料清单数据验证数量
        bill_data = self.get_bill_data(picking_id)
        if "error" in bill_data:
            return {"success": False, "message": bill_data["error"]}
        
        # 分组统计品牌型号中的物料数量
        model_qty_by_item = {}
        for model in brand_models:
            item_code = model.get("item", "")
            if not item_code:
                continue
                
            qty = model.get("quantity", 0)
            if item_code in model_qty_by_item:
                model_qty_by_item[item_code] += qty
            else:
                model_qty_by_item[item_code] = qty
        
        # 检查自制件是否全部签单（根据前端传递的数据是否包含自制件判断）
        has_self_made = False
        for model in brand_models:
            item_code = model.get("item", "")
            for self_made_item in bill_data["self_made_items"]:
                if self_made_item["item"] == item_code:
                    has_self_made = True
                    break
            if has_self_made:
                break
        
        # 检查自制件签单数量
        if has_self_made:
            # 如果签单包含自制件，需检查所有自制件是否都已签单
            for item in bill_data["self_made_items"]:
                item_code = item["item"]
                if item_code not in model_qty_by_item:
                    return {"success": False, "message": f"自制件 {item_code} 未完全签单"}
                if model_qty_by_item[item_code] != item["qty"]:
                    return {"success": False, "message": f"自制件 {item_code} 签单数量与需求数量不符"}
        
        # 检查外购件签单数量
        for item in bill_data["purchased_items"]:
            item_code = item["item"]
            if item_code not in model_qty_by_item:
                return {"success": False, "message": f"外购件 {item_code} 未完全签单"}
            if model_qty_by_item[item_code] != item["qty"]:
                return {"success": False, "message": f"外购件 {item_code} 签单数量与需求数量不符"}
        
        # 5. 构建SQL语句列表
        now = datetime.datetime.now()
        sql_list = []
        
        # 5.1 插入签单数据
        for model in brand_models:
            sql = """
            INSERT INTO SignData(Con_PickingID, ContractNO, Item, Brand, Model, Origin, PCS) 
            VALUES(?, ?, ?, ?, ?, ?, ?)
            """
            sql_list.append((sql, (
                picking_id, 
                contract_no, 
                model.get("item", ""), 
                model.get("brand", ""), 
                model.get("model", ""), 
                model.get("origin", ""), 
                model.get("quantity", 0)
            )))
        
        # 5.2 更新识别码状态
        sql = "UPDATE Con_Picking SET SignTime=?, PackageQtyStatus=6 WHERE Con_PickingID=?"
        sql_list.append((sql, (now, picking_id)))
        
        # 5.3 如果是最后一份单，更新合同状态
        remain_num = self.position_num(contract_no)
        if remain_num == 1:
            # 获取拍照状态
            photo_status = self.con_is_photo(contract_no)
            
            # 获取齐套时间，计算签单周期
            qt_time = self.get_con_qt_time(contract_no)
            if qt_time:
                # 计算签单周期（工作日）
                qd_cycle = self.calculate_cycle(qt_time, now)
                # 计算夜间小时数
                night_hours = self.calculate_night_hours(qt_time, now)
            else:
                qd_cycle = 0
                night_hours = 0
            
            # 更新合同状态
            sql = """
            UPDATE Contract SET QD_Time=?, QDCycle=?, QD_Cycle=?, 
            PackageQtyStatus=?, CurrentException=0 WHERE ContractNO=?
            """
            sql_list.append((sql, (now, qd_cycle, night_hours, photo_status, contract_no)))
            
            # 更新变更状态
            sql = "UPDATE Change SET Step7=0"
            sql_list.append((sql, None))
        
        # 6. 执行事务
        success = self.execute_transaction(sql_list)
        
        # 7. 如果是最后一份单且提交成功，处理多品牌型号的编码
        if success and remain_num == 1:
            try:
                self.process_different_brands(contract_no)
            except Exception as e:
                print(f"处理多品牌型号出错: {e}")
        
        return {
            "success": success,
            "message": "提交成功" if success else "提交失败"
        }
    
    def get_con_qt_time(self, contract_no: str) -> Optional[datetime.datetime]:
        """
        获取合同齐套时间
        Args:
            contract_no: 合同号
        Returns:
            齐套时间
        """
        sql = "SELECT QT_Time FROM Contract WHERE ContractNO=?"
        try:
            row = self.db.Query_one(sql, (contract_no,))
            if not row or not row.QT_Time:
                return None
            return row.QT_Time
        except Exception as e:
            print(f"获取齐套时间出错: {e}")
            return None
    
    def calculate_cycle(self, start_time: datetime.datetime, end_time: datetime.datetime) -> int:
        """
        计算工作日周期（不包含节假日）
        Args:
            start_time: 开始时间
            end_time: 结束时间
        Returns:
            工作日天数
        """
        # 简化实现，实际应根据节假日数据计算
        days = (end_time - start_time).days
        # 简单估算工作日，减去周末天数
        weekends = (days // 7) * 2
        remaining_days = days % 7
        # 检查剩余天数中的周末
        start_weekday = start_time.weekday()
        for i in range(remaining_days):
            if (start_weekday + i) % 7 >= 5:  # 5和6是周六和周日
                weekends += 1
        return max(0, days - weekends)
    
    def calculate_night_hours(self, start_time: datetime.datetime, end_time: datetime.datetime) -> int:
        """
        计算夜间小时数
        Args:
            start_time: 开始时间
            end_time: 结束时间
        Returns:
            夜间小时数
        """
        # 简化实现，按照C#代码中的逻辑来计算夜间小时数
        # 实际应考虑工作日的夜间时段(如18:00-次日8:00)和节假日
        days = (end_time - start_time).days
        return days * 12  # 假设每天12小时是夜间
    
    def process_different_brands(self, contract_no: str) -> bool:
        """
        处理多品牌型号的编码，汇总到Different表
        
        Args:
            contract_no: 合同号
            
        Returns:
            bool: 处理成功返回True，失败返回False
        """
        try:
        # 获取签单数据
            query = """
        SELECT Item, Brand, Model, Origin, SUM(PCS) as TotalPCS
        FROM SignData
                WHERE ContractNO = ?
        GROUP BY Item, Brand, Model, Origin
        HAVING COUNT(DISTINCT Brand + Model + Origin) > 1
        """
            
            # 执行查询
            rows = self.db.Query_all(query, (contract_no,))
            if not rows:
                self.logger.info(f"合同 {contract_no} 没有需要处理的多品牌型号数据")
                return True
            
            # 准备插入语句
            insert_sql = """
                INSERT INTO Different (ContractNO, Item, Brand, Model, Origin, PCS)
                VALUES (?, ?, ?, ?, ?, ?)
            """
            
            # 构建批量插入的参数列表
            sql_params = []
            for row in rows:
                params = (
                    contract_no,
                    row.Item,
                    row.Brand,
                    row.Model,
                    row.Origin,
                    row.TotalPCS
                )
                sql_params.append((insert_sql, params))
            
            # 执行批量插入
            if not sql_params:
                self.logger.warning(f"合同 {contract_no} 没有有效的多品牌型号数据需要插入")
                return True
                
            success = self.execute_transaction(sql_params)
            if success:
                self.logger.info(f"合同 {contract_no} 的多品牌型号数据处理成功")
            else:
                self.logger.error(f"合同 {contract_no} 的多品牌型号数据处理失败")
            
            return success
            
        except Exception as e:
            self.logger.error(f"处理多品牌型号数据时发生错误: {str(e)}")
            return False
    
    def execute_transaction(self, sql_list):
        """
        执行事务
        Args:
            sql_list: 可以是SQL语句字符串列表，也可以是(sql, params)元组列表
        Returns:
            执行结果
        """
        if not sql_list:
            return True  # 没有语句需要执行，视为成功
        try:
            with self.db.connect() as conn:
                cursor = conn.cursor()
                # 关闭自动提交
                conn.autocommit = False
                # 执行每条SQL语句
                for sql_item in sql_list:
                    # 检查sql_item是否为元组(包含sql和params)或直接为sql字符串
                    if isinstance(sql_item, tuple) and len(sql_item) > 1:
                        sql = sql_item[0]
                        params = sql_item[1]
                        if params is not None:
                            cursor.execute(sql, params)
                        else:
                            cursor.execute(sql)
                    else:
                        # 直接执行SQL字符串
                        cursor.execute(sql_item)
                    # 提交事务
                conn.commit()
                return True
        except Exception as e:
            self.logger.error(f"执行事务失败: {str(e)}")
            # 事务回滚会在contextmanager中自动处理
            return False

    def get_contract_date_time(self, contract_no: str, type_code: int) -> str:
        """
        获取合同的特定时间
        
        Args:
            contract_no: 合同号
            type_code: 时间类型代码，2表示齐套时间
            
        Returns:
            合同时间
        """
        try:
            # 根据type_code选择不同的时间字段
            time_field = "QT_Time" if type_code == 2 else "Creation_Date"
            
            sql = f"SELECT {time_field} FROM Contract WHERE ContractNO='{contract_no}'"
            result = self.db.Query_one(sql)
            if result and hasattr(result, time_field):
                return getattr(result, time_field)
            return None
        except Exception as e:
            self.logger.error(f"获取合同时间失败: {str(e)}")
            raise e

    def differ_date(self, start_time, end_time, differ_type: int) -> int:
        """
        计算两个日期之间的差值（秒数）
        
        Args:
            start_time: 开始时间
            end_time: 结束时间
            differ_type: 差值类型，1表示除去指定时间范围，2表示不除去指定时间范围
            
        Returns:
            日期差值（秒数）
        """
        try:
            # 确保时间类型一致
            if isinstance(start_time, str):
                start_time = datetime.fromisoformat(start_time.replace('Z', '+00:00'))
            if isinstance(end_time, str):
                end_time = datetime.fromisoformat(end_time.replace('Z', '+00:00'))
            
            # 检查结束时间是否小于开始时间
            if end_time < start_time:
                return 0
            
            # 获取节假日列表（简化实现，实际应从数据库或配置获取）
            holidays_list = self.get_holidays_list()
            
            # 计算相差天数
            sdate = start_time.strftime("%Y-%m-%d")
            edate = end_time.strftime("%Y-%m-%d")
            time_span = end_time - start_time
            int_differ = time_span.days  # 相差天数
            
            # 统计中间工作日天数（不包括首尾两天，也不包括节假日）
            workday = 0
            for i in range(1, int_differ + 1):
                current_date = (start_time + datetime.timedelta(days=i)).strftime("%Y-%m-%d")
                if current_date != edate and current_date not in holidays_list:
                    workday += 1
            
            # 计算总秒数
            second_sum = 0
            
            if differ_type == 1:  # 计算除去指定时间内和节假日的秒数
                second_sum = workday * 39600  # 39600=11小时*3600秒
                
                # 如果是同一天并且不是节假日
                if sdate == edate and sdate not in holidays_list:
                    second_sum += self.fun1(start_time, end_time)
                    
                # 如果不是同一天
                elif sdate != edate:
                    # 计算开始这天的秒数（如果不是节假日）
                    if sdate not in holidays_list:
                        second_sum += self.fun2(start_time)
                    
                    # 计算结束这天的秒数（如果不是节假日）
                    if edate not in holidays_list:
                        second_sum += self.fun3(end_time)
                        
            elif differ_type == 2:  # 计算没有除去指定时间但是除去了节假日的秒数
                second_sum = workday * 86400  # 86400=24小时*3600秒
                
                # 如果是同一天并且不是节假日
                if sdate == edate and sdate not in holidays_list:
                    # 计算两个时间之间的秒数差
                    time_diff = end_time - start_time
                    second_sum += time_diff.total_seconds()
                    
                # 如果不是同一天
                elif sdate != edate:
                    # 计算开始这天的秒数（如果不是节假日）
                    if sdate not in holidays_list:
                        # 计算从start_time到当天23:59:59的秒数
                        day_end = datetime.datetime.combine(start_time.date(), datetime.time(23, 59, 59))
                        time_diff = day_end - start_time
                        second_sum += time_diff.total_seconds()
                    
                    # 计算结束这天的秒数（如果不是节假日）
                    if edate not in holidays_list:
                        # 计算从当天00:00:00到end_time的秒数
                        day_start = datetime.datetime.combine(end_time.date(), datetime.time(0, 0, 0))
                        time_diff = end_time - day_start
                        second_sum += time_diff.total_seconds()
            
            return second_sum
        except Exception as e:
            self.logger.error(f"计算日期差值失败: {str(e)}")
            return 0
    
    def get_holidays_list(self) -> List[str]:
        """
        获取节假日列表
        
        Returns:
            节假日列表，格式为["YYYY-MM-DD"]，如果发生错误返回["1900-01-01"]
        """
        sql = "SELECT StatutoryholidaysTime FROM Statutoryholidays"
        holidays = ["1900-01-01"]  # 默认返回值
        
        try:
            # 查询节假日数据
            rows = self.db.Query_all(sql)
            if not rows:
                self.logger.info("未找到节假日数据，使用默认日期")
                return holidays
            
            # 转换日期格式并添加到列表
            holidays = []
            for row in rows:
                if not hasattr(row, 'StatutoryholidaysTime'):
                    self.logger.warning("节假日数据格式错误：缺少StatutoryholidaysTime字段")
                    continue
                    
                holiday_date = row.StatutoryholidaysTime
                if not holiday_date:
                    self.logger.warning("节假日数据为空")
                    continue
                
                try:
                    # 将日期转换为yyyy-MM-dd格式
                    date_str = holiday_date.strftime("%Y-%m-%d")
                    holidays.append(date_str)
                except (AttributeError, ValueError) as e:
                    self.logger.error(f"日期格式转换错误: {holiday_date}, {str(e)}")
                    continue
            
            # 如果转换后列表为空，返回默认日期
            if not holidays:
                self.logger.warning("转换后的节假日列表为空，使用默认日期")
                return ["1900-01-01"]
                
            return holidays
            
        except Exception as e:
            self.logger.error(f"获取节假日列表失败: {str(e)}")
            return ["1900-01-01"]
    
    def fun1(self, d1, d2):
        """
        计算同一天内两个时间之间的工作秒数
        
        Args:
            d1: 开始时间
            d2: 结束时间
            
        Returns:
            工作秒数
        """
        # 提取时间部分
        time_d1 = datetime.time(d1.hour, d1.minute, d1.second)
        time_d2 = datetime.time(d2.hour, d2.minute, d2.second)
        
        # 工作时间范围
        time_830 = datetime.time(8, 30, 0)
        time_21 = datetime.time(21, 0, 0)
        
        # 计算秒数
        jg = 0
        
        # 如果d1<=8:30
        if time_d1 <= time_830:
            if time_d2 <= time_21:
                # 从8:30到d2的秒数
                seconds_830_to_d2 = (datetime.datetime.combine(datetime.date.today(), time_d2) - 
                                     datetime.datetime.combine(datetime.date.today(), time_830)).total_seconds()
                jg = seconds_830_to_d2
            else:  # time_d2 >= time_21
                # 从8:30到21:00的秒数 (12.5小时 = 45000秒)
                jg = 45000
        
        # 如果8:30 <= d1 <= 21:00
        elif time_830 <= time_d1 <= time_21:
            if time_d2 <= time_21:
                # 从d1到d2的秒数
                seconds_d1_to_d2 = (datetime.datetime.combine(datetime.date.today(), time_d2) - 
                                    datetime.datetime.combine(datetime.date.today(), time_d1)).total_seconds()
                jg = seconds_d1_to_d2
            else:  # time_d2 >= time_21
                # 从d1到21:00的秒数
                seconds_d1_to_21 = (datetime.datetime.combine(datetime.date.today(), time_21) - 
                                    datetime.datetime.combine(datetime.date.today(), time_d1)).total_seconds()
                jg = seconds_d1_to_21
        
        # 如果d1>=21:00
        elif time_d1 >= time_21:
            jg = 0
            
        return jg
    
    def fun2(self, d1):
        """
        计算开始日期（非结束日期）的工作秒数
        
        Args:
            d1: 开始时间
            
        Returns:
            工作秒数
        """
        # 提取时间部分
        time_d1 = datetime.time(d1.hour, d1.minute, d1.second)
        
        # 工作时间范围
        time_830 = datetime.time(8, 30, 0)
        time_21 = datetime.time(21, 0, 0)
        
        # 计算秒数
        jg = 0
        
        # 如果d1<=8:30
        if time_d1 <= time_830:
            # 从8:30到21:00的秒数 (12.5小时 = 45000秒)
            jg = 45000
        
        # 如果d1<=21:00
        elif time_d1 <= time_21:
            # 从d1到21:00的秒数
            seconds_d1_to_21 = (datetime.datetime.combine(datetime.date.today(), time_21) - 
                                datetime.datetime.combine(datetime.date.today(), time_d1)).total_seconds()
            jg = seconds_d1_to_21
        
        # 如果d1>=21:00
        elif time_d1 >= time_21:
            jg = 0
            
        return jg
    
    def fun3(self, d2):
        """
        计算结束日期（非开始日期）的工作秒数
        
        Args:
            d2: 结束时间
            
        Returns:
            工作秒数
        """
        # 提取时间部分
        time_d2 = datetime.time(d2.hour, d2.minute, d2.second)
        
        # 工作时间范围
        time_830 = datetime.time(8, 30, 0)
        time_21 = datetime.time(21, 0, 0)
        
        # 计算秒数
        jg = 0
        
        # 如果d2<=8:30
        if time_d2 <= time_830:
            jg = 0
        
        # 如果d2<=21:00
        elif time_d2 <= time_21:
            # 从8:30到d2的秒数
            seconds_830_to_d2 = (datetime.datetime.combine(datetime.date.today(), time_d2) - 
                                 datetime.datetime.combine(datetime.date.today(), time_830)).total_seconds()
            jg = seconds_830_to_d2
        
        # 如果d2>=21:00
        elif time_d2 >= time_21:
            # 从8:30到21:00的秒数 (12.5小时 = 45000秒)
            jg = 45000
            
        return jg

    def get_sign_data_item(self, contract_no: str):
        """
        获取签单表中有多种品牌和型号的编码数据
        
        Args:
            contract_no: 合同号
            
        Returns:
            包含多品牌型号编码数据的结果集
        """
        try:
            sql = f"""
            SELECT ContractNO, Item, Brand, Model, Origin, SUM(PCS) AS sumPCS, COUNT(DISTINCT(Item+Brand+Model+Origin)) AS counts 
            FROM SignData 
            WHERE ContractNO='{contract_no}' 
            GROUP BY ContractNO, Item, Brand, Model, Origin 
            HAVING Item IN (
                SELECT Item FROM (
                    SELECT Item, COUNT(DISTINCT(Item+Brand+Model+Origin)) AS qty 
                    FROM SignData 
                    WHERE ContractNO='{contract_no}' 
                    GROUP BY Item 
                    HAVING COUNT(DISTINCT(Item+Brand+Model+Origin)) > 1
                ) AS a
            )
            """
            return self.db.Query_all(sql)
        except Exception as e:
            self.logger.error(f"获取多品牌型号数据失败: {str(e)}")
            return None

    def save_to_different_table(self, data):
        """
        将数据保存到Different表
        
        Args:
            data: 要保存的数据
            
        Returns:
            保存是否成功
        """
        try:
            # 实际实现取决于data的格式和Different表的结构
            # 这里假设data是行集合
            sql_list = []
            
            for row in data:
                # 获取行中的值
                contract_no = row.ContractNO if hasattr(row, 'ContractNO') else ''
                item = row.Item if hasattr(row, 'Item') else ''
                brand = row.Brand if hasattr(row, 'Brand') else ''
                model = row.Model if hasattr(row, 'Model') else ''
                origin = row.Origin if hasattr(row, 'Origin') else ''
                sum_pcs = row.sumPCS if hasattr(row, 'sumPCS') else 0
                counts = row.counts if hasattr(row, 'counts') else 0
                
                # 创建Insert语句
                sql = """
                INSERT INTO Different (ContractNO, Item, Brand, Model, Origin, sumPCS, counts)
                VALUES (?, ?, ?, ?, ?, ?, ?)
                """
                params = (contract_no, item, brand, model, origin, sum_pcs, counts)
                sql_list.append((sql, params))
            
            return self.execute_transaction(sql_list)
        except Exception as e:
            self.logger.error(f"保存到Different表失败: {str(e)}")
            return False

    def get_photo_required_items(self, contract_no: str):
        """获取需要拍照的物料列表"""
        try:
            query = "SELECT Item FROM ConItem WHERE ContractNO=? AND ISPhoto='Y'"
            result = self.db.Query_all(query, (contract_no,))
            if not result:
                return {"code": 200, "data": []}
            return {"code": 200, "data": [row.Item for row in result]}
        except Exception as e:
            self.logger.error(f"获取需要拍照的物料列表失败: {str(e)}")
            return {"code": 500, "message": "获取需要拍照的物料列表失败"} 